﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using ExcelLibrary;
using ExcelLibrary.CompoundDocumentFormat;
using ExcelLibrary.SpreadSheet;
using ExcelLibrary.BinaryFileFormat;
using QiHe.CodeLib;
using RMS.DB;
using System.Diagnostics;

namespace RMS
{
    public partial class frmImportProduct : Form
    {
        List<Inventory> listProduct = null;
        public frmImportProduct()
        {
            InitializeComponent();
            lblResultMsg.Text = "";
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            lblResultMsg.Text = "";

            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtExcelFilePath.Text = openFileDialog1.FileName;
            }

            btnReadData.Enabled = true;
        }

        private void btnReadData_Click(object sender, EventArgs e)
        {
            string excelFilePath = txtExcelFilePath.Text.Trim();

            //Check exist
            if (!File.Exists(excelFilePath)) return;

            int rowCountImported = 0;
            int rowCountTotal = 0;
            try
            {
                using (CompoundDocument doc = CompoundDocument.Open(excelFilePath))
                {
                    byte[] bookdata = doc.GetStreamData("Workbook");
                    if (bookdata == null)
                        return;
                    Workbook book = WorkbookDecoder.Decode(new MemoryStream(bookdata));
                    //ExtractImages(book, @"C:\Images");
                    
                    foreach (Worksheet sheet in book.Worksheets)
                    {
                        listProduct = new List<Inventory>();

                        DataGridView dgvCells = new DataGridView();
                        dgvCells.Dock = DockStyle.Fill;
                        dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
                        dgvCells.ColumnCount = sheet.Cells.LastColIndex + 1;
                        rowCountTotal = sheet.Cells.LastRowIndex;
                        // tranverse cells
                        //foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
                        //{
                        //    dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
                        //    if (cell.Right.Style.BackColor != Color.White)
                        //    {
                        //        dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
                        //    }
                        //}
                        // tranvers rows by Index
                        for (int rowIndex = sheet.Cells.FirstRowIndex + 1; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                        {
                            Row row = sheet.Cells.GetRow(rowIndex);
                            Inventory item = null;
                            try
                            {
                                NewRow://Label: Start a new row
                                item = new Inventory();
                                
                                for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
                                {
                                    Cell cell = row.GetCell(colIndex);
                                    
                                    switch (colIndex)
                                    {
                                        case 0:
                                            if (string.IsNullOrEmpty(cell.StringValue)) goto NewRow;
                                            item.ID = cell.StringValue;
                                            break;
                                        case 1:
                                            item.Name = cell.StringValue;
                                            break;
                                        case 2:
                                            item.Quantity = Convert.ToInt32(cell.Value);
                                            break;
                                        case 3:
                                            item.ImportPrice = Convert.ToDecimal(cell.Value);
                                            break;
                                        case 4:
                                            item.SalePrice = Convert.ToDecimal(cell.Value);
                                            break;
                                        case 5:
                                            item.TotalPrice = Convert.ToDecimal(cell.Value);
                                            break;
                                        case 6:
                                            item.SupplierID = cell.StringValue;
                                            break;
                                        case 7:
                                            item.Category = cell.StringValue;
                                            break;
                                        case 8:
                                            item.StyleId = cell.StringValue;
                                            break;
                                        case 9:
                                            item.Weight = Convert.ToDecimal(cell.Value);
                                            break;
                                        case 10:
                                            item.Volume = Convert.ToDecimal(cell.Value);
                                            break;
                                        case 11: 
                                            item.Image =  cell.StringValue;
                                            break;
                                        case 12:
                                            item.InvoiceNo = cell.StringValue;
                                            break;
                                        case 13:
                                            item.MemoNo = cell.StringValue;
                                            break;
                                        case 14:
                                            item.ItemNo = cell.StringValue;
                                            break;
                                        case 15:
                                            item.StyleNo = cell.StringValue;
                                            break;
                                        case 16:
                                            item.Description = cell.StringValue;
                                            break;
                                    }


                                }

                                
                            }
                            catch(Exception)
                            {

                            }

                            if (!string.IsNullOrEmpty(item.ID))
                            {
                                rowCountImported += 1;
                                listProduct.Add(item);
                            }
                            
                            
                        }




                        // tranvers rows directly
                        //foreach (KeyValuePair<int, Row> row in sheet.Cells.Rows)
                        //{
                        //    foreach (KeyValuePair<int, Cell> cell in row.Value)
                        //    {
                        //        var cellValue = cell.Value;
                        //    }
                        //}
                        //foreach (KeyValuePair<Pair<int, int>, Picture> cell in sheet.Pictures)
                        //{
                        //    int rowIndex = cell.Key.Left;
                        //    int colIndex = cell.Key.Right;
                        //    if (dgvCells.RowCount < rowIndex + 1)
                        //    {
                        //        dgvCells.RowCount = rowIndex + 1;
                        //    }
                        //    if (dgvCells.ColumnCount < colIndex + 1)
                        //    {
                        //        dgvCells.ColumnCount = colIndex + 1;
                        //    }
                        //    dgvCells[colIndex, rowIndex].Value = String.Format("<Image,{0}>", cell.Value.Image.FileExtension);
                        //}
                        //sheetPage.Controls.Add(dgvCells);
                        //tabControl1.TabPages.Add(sheetPage);
                    }
                    dataGridView1.AutoGenerateColumns = false;
                    dataGridView1.DataSource = listProduct;
                    
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi khi đọc dữ liệu. Hãy đóng file Excel và thử lại.");
            }

            lblResultMsg.Text = string.Format("{0} sản phẩm hợp lệ trong tổng số {1}.", rowCountImported, rowCountTotal);
            btnImport.Enabled = true;

        }

        private void btnImport_Click(object sender, EventArgs e)
        {            
            InventoryMng mng = new InventoryMng();
            int result = mng.ImportProducts(listProduct);
            lblResultMsg.Text = result < 1 ? "Không sản phẩm nào được nhập thành công. Vui lòng thử lại." : result + " sản phẩm được nhập thành công vào cơ sở dữ liệu.";

            btnImport.Enabled = false;
        }

        private void linkOpenTemplateFile_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string path = Environment.CurrentDirectory + "\\ImportProductFromExcelFileTemplate.xls";
            Process.Start(path);
            
        }

        

    
    }
}


